package benny_driving_ser.dao.impl;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import benny_driving_ser.ObjectBean.DriHistoryOrderBean;
import benny_driving_ser.ObjectBean.UserHistoryOrderBean;
import benny_driving_ser.ObjectBean.UserOrderBean;
import benny_driving_ser.dao.OrderDao;
import benny_driving_ser.util.DBUtil;

public class OrderDaoImpl implements OrderDao {

	private List<DriHistoryOrderBean> list;  
	private DriHistoryOrderBean driHB;
	private List<UserOrderBean> userOrders;
	private UserOrderBean userOrder;
	private UserHistoryOrderBean userHistoryOrder;
	private List<UserHistoryOrderBean> userHistoryOrders;

	/**
	 * 加载司机历史订单
	 */
	public List<DriHistoryOrderBean> loadDriHistoryOrder(int driId,int startPage) { 

//		if(startPage>=0){
//			startPage=1;
//		} 
		
		
//		String sql = "select t.id,t.start_time,t.end_time,t.gk,t.gksj,u.xm,t.qd,"
//				+ "t.zd,t.sjqd,t.sjzd,t.ssfy,t.pay,t.sjlc,t.jl,t.jg,t.ksje "
//				+ "from t_djpd t,t_user u where t.dri_id= " + driId + " " +
//				" and t.state>5 and t.dri_id = u.id ORDER BY end_time DESC";
//				
		
		String sql = "select t.id,t.start_time,t.end_time,t.gk,t.gksj,u.xm,t.qd,"
				+ "t.zd,t.sjqd,t.sjzd,t.ssfy,t.pay,t.sjlc,t.jl,t.jg,t.ksje,t.bonus_cost,t.bz,t.paycode_cost "
				+ "from t_djpd t,t_user u where t.dri_id= " + driId + " " +
				" and t.state>5 and t.dri_id = u.id ORDER BY end_time DESC LIMIT ?,30";

		Connection con = DBUtil.getInstance().openConnection();
		list = new ArrayList<DriHistoryOrderBean>();

		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, startPage);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				driHB = new DriHistoryOrderBean();
				driHB.setAdesPos(rs.getString("sjzd") + "");
				driHB.setAstartPos(rs.getString("sjqd") + "");
				driHB.setCost(rs.getString("ssfy") + "");
				driHB.setCostWay(rs.getString("pay") + "");
				/*
				 * 扣税金额(总价*0.07)(保留小数点后两位)
				 *  d=((int)(d*100))/100
				 */
				//原来扣税金额为是收费用的7%,12.6改为直接从数据库拿出来 字段为ksje
				//driHB.setDedCost(decimals2(rs.getInt("total"))+"");
				driHB.setDedCost(decimals2(rs.getInt("ksje")));//小数点后两位
				driHB.setDesPos(rs.getString("zd") + "");
				driHB.setDriName(rs.getString("xm") + "");
				driHB.setBonus(rs.getString("bonus_cost")+"");
				driHB.setBz(rs.getString("bz")+"");
				driHB.setPayCodeCost(rs.getString("paycode_cost")==null?"0":rs.getString("paycode_cost")+"");
				
				if(rs.getString("start_time")==null){
					driHB.setStartTime("null");
				}else{
					driHB.setStartTime(rs.getString("start_time").substring(0,rs.getString("start_time").length()-2) + "");
				}
				
				if(rs.getString("end_time")==null){
					driHB.setEndTime("null");
				}else{
					driHB.setEndTime(rs.getString("end_time").substring(0,rs.getString("end_time").length()-2));
				}
				
				driHB.setExCost(rs.getString("jg") + "");
				driHB.setExMil(rs.getString("jl") + "");
				driHB.setFormId(rs.getString("id") + "");
				driHB.setMil(rs.getString("sjlc") + "");
				driHB.setStartsPos(rs.getString("qd") + "");
				driHB.setUserName(rs.getString("gk") + "");
				driHB.setUserTel(rs.getString("gksj") + "");

				list.add(driHB);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
//		catch (ParseException e){
//			e.printStackTrace();
//		}
		
		DBUtil.getInstance().closeConnection(con);
		return list == null ? null : list;
	}

	/**
	 * 加载用户预约订单
	 */
	public List<UserOrderBean> loadUserOrder(int userId) {

		String sql = "select d.id,d.yyrq,d.gk,d.gksj,d.qd,d.zd,d.jl,d.jg,d.state,d.dri_id," +
				" c.card_cost,c.card_mil,c.times,c.card_state" +
				" from t_djpd d,t_card c where d.user_id = "+userId+" AND d.state<7 AND c.id = d.card_id";
		
		String sql_driId = "select u.xm,u.username,u.dh from t_user u  where u.id=?";

		Connection con = DBUtil.getInstance().openConnection();
		userOrders = new ArrayList<UserOrderBean>();
		
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			while (rs.next()) {
				
				
				userOrder = new UserOrderBean();
				int i = 0;
				String dri_id = "";
				if ((dri_id = rs.getString("dri_id")) == null) {
					userOrder.setDriName("");
					userOrder.setDriNum("");
					userOrder.setDriTel("");
				} else {
					PreparedStatement ps2 = con.prepareStatement(sql_driId);
					ps2.setInt(1, Integer.parseInt(dri_id.trim()));
					ResultSet rs2 = ps2.executeQuery();
					if (rs2.next()) {
						userOrder.setDriName(rs2.getString("xm") + "");
						userOrder.setDriNum(rs2.getString("username") + "");
						userOrder.setDriTel(rs2.getString("dh") + "");
					}
				}
				userOrder.setDesPos(rs.getString("zd") + "");
				userOrder.setExcost(rs.getString("jg") + "");
				userOrder.setExmil(rs.getString("jl")+"");
				userOrder.setFormId(rs.getString("id") + "");
				userOrder.setFormState(rs.getString("state") + "");
				userOrder.setStartPos(rs.getString("qd") + "");
				userOrder.setTime(rs.getString("yyrq") + "");
				userOrder.setUserName(rs.getString("gk") + "");
				userOrder.setUserTel(rs.getString("gksj") + "");
				
				i = rs.getInt("card_state");
				if(i== 1){//为金额
					userOrder.setCardValue(rs.getString("card_cost"));
				}else if(i==2){//公里数
					userOrder.setCardValue(rs.getString("card_mil"));
				}else if(i==3){//次数
					userOrder.setCardValue(rs.getString("times"));
				}else{
					userOrder.setCardValue("null");
				}
				userOrder.setCardState(rs.getString("card_state"));
				userOrders.add(userOrder);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.getInstance().closeConnection(con);
		return userOrders == null ? null : userOrders;
	}

	/**
	 * 加载用户历史订单
	 */
	public List<UserHistoryOrderBean> loadUserHistoryOrder(int userId) {
		String sql = "select t.id,t.gk,t.gksj,t.sowt,u.xm,u.dh,"
				+ "u.username,t.qd,t.zd,t.start_time,t.end_time,t.ssfy,"
				+ "t.pay,t.sjlc ,t.isc from t_djpd t,t_user u " +
				"where t.user_id = " + userId+ " AND u.id = t.dri_id AND t.state = 7";

		Connection con = DBUtil.getInstance().openConnection();
		userHistoryOrders = new ArrayList<UserHistoryOrderBean>();
		try {
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			PreparedStatement ps = con.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				userHistoryOrder = new UserHistoryOrderBean();

				userHistoryOrder.setCost(rs.getString("ssfy") + "");
				userHistoryOrder.setCostWay(rs.getString("pay") + "");
				userHistoryOrder.setDesPos(rs.getString("zd") + "");
				userHistoryOrder.setDriName(rs.getString("xm") + "");
				userHistoryOrder.setDriNum(rs.getString("username") + "");
				userHistoryOrder.setDriTel(rs.getString("dh") + "");
				if(rs.getString("end_time")==null){
					userHistoryOrder.setEndTime("null");
				}else{
					userHistoryOrder.setEndTime(rs.getString("end_time").substring(0,rs.getString("end_time").length()-2));
				}
				userHistoryOrder.setFormId(rs.getString("id") + "");
				userHistoryOrder.setIsComment(rs.getString("isc") + "");
				userHistoryOrder.setMil(rs.getString("sjlc") + "");
				userHistoryOrder.setStartPos(rs.getString("qd") + "");
				if(rs.getString("start_time")==null){
					userHistoryOrder.setStartTime("null");
				}else{
					userHistoryOrder.setStartTime(rs.getString("start_time").substring(0,rs.getString("start_time").length()-2) + "");
				}
				userHistoryOrder.setUserName(rs.getString("gk") + "");
				userHistoryOrder.setUserTel(rs.getString("gksj") + "");
				userHistoryOrder.setWaitTime(rs.getString("sowt") + "");

				userHistoryOrders.add(userHistoryOrder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		DBUtil.getInstance().closeConnection(con);
		return userHistoryOrders == null ? null : userHistoryOrders;
	}
	
	/**
	 * 小数点后两位
	 * @param i
	 *  * 计算扣税金额,保留小数点后两位
	 * @param total
	 * @return
	 */
	public String decimals2(int i){
		DecimalFormat df = new DecimalFormat("#.##");
		//double tax = total * 0.07;
		String taxs = df.format(i);
		double dou = Double.parseDouble(taxs);
		String s = dou+"";
		
		//System.out.println("扣税金额:"+i);
		return s;
	}
	
	/**
	 * 生成电话订单
	 */
	public int createTelOrder(int userId,int driId) {
		String sql = "insert into t_djpd(user_id,dri_id) VALUES("+userId+","+driId+")";
		try {
			Connection con = DBUtil.getInstance().openConnection();
			PreparedStatement pst = con.prepareStatement(sql);
			pst.executeUpdate();
			DBUtil.getInstance().closeConnection(con);
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1;
	}
	
	
	/**
	 * 补充电话订单
	 * @return
	 */
	
	public int addTelOrderInfo(int formId){
		
		
		return 0;
	}
	
	/**
	 * 司机提现
	 */
	public int getDeposit(int driId,int cost) {
		String sql_balance = "select ye from t_user where id="+driId;
		String sql_getDeposit = "update t_user set ye=?";
		
		Connection con = DBUtil.getInstance().openConnection();
		try {
			System.out.println("cost:"+cost);
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			con.setAutoCommit(false);
			PreparedStatement pst_balance = con.prepareStatement(sql_balance);
			ResultSet rs = pst_balance.executeQuery(sql_balance);
			int balance = 0;
			while(rs.next()){
				balance = rs.getInt("ye");
				System.out.println("balance:"+balance);
			}
			if(balance<cost){
				//余额不足
				System.out.println("余额不足");
				return 2;
			}
			PreparedStatement pst_getDeposit = con.prepareStatement(sql_getDeposit);
			pst_getDeposit.setInt(1, (balance-cost));
			pst_getDeposit.executeUpdate();
			System.out.println("提现成功.");
			//提现成功
			con.commit();
			DBUtil.getInstance().closeConnection(con);
			
			return 0;
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return 1;
		
	}

//	/**
//	 * 34号接口
//	 * 司机充值
//	 */
//	public int driPay(int driId,int cost){
//		String sql_balance = "select ye from t_user where id = "+driId;
//		String sql_driPay = "update t_user set ye = ? where id = "+driId;
//		Connection con = DBUtil.getInstance().openConnection();
//		try{
//			con.setAutoCommit(false);
//			PreparedStatement pst_balance = con.prepareStatement(sql_balance);
//			ResultSet rs = pst_balance.executeQuery();
//			int balance = 0;
//			while(rs.next()){
//			balance = rs.getInt("ye");
//			}
//			System.out.println("balance:"+balance);
//			PreparedStatement pst_driPay = con.prepareStatement(sql_driPay);
//			pst_driPay.setInt(1, (balance+cost));
//			pst_driPay.executeUpdate();
//			con.commit();
//			DBUtil.getInstance().closeConnection(con);
//			
//			return 0;
//		}catch(SQLException e){
//			e.printStackTrace();
//		}
//		
//		return 1;
//	}
	
	
	/**
	 * 31号接口
	 * 加载司机充值记录
	 * @param driId
	 * @return
	 */
	public int loadDriPayRecord(int driId){
		return 0;
	}
	
	
	/**
	 * 司机未完成订单
	 * @param driId
	 * @return
	 */
	public int driUnfinishedOrder(int driId){
		int count = 10;
		String sql = "select count(*) from t_djpd where dri_id = "+driId+" and 2<state AND state<7";
		try {
			Connection con = DBUtil.getInstance().openConnection();
			con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rs = pst.executeQuery(sql);
			while(rs.next()){
				count = rs.getInt(1);
				System.out.println("count:"+count);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return count;
	}
	
	public List<DriHistoryOrderBean> getList() {
		return list;
	}

	public void setList(List<DriHistoryOrderBean> list) {
		this.list = list;
	}

	public DriHistoryOrderBean getDriHB() {
		return driHB;
	}

	public void setDriHB(DriHistoryOrderBean driHB) {
		this.driHB = driHB;
	}

	public List<UserOrderBean> getUserOrders() {
		return userOrders;
	}

	public void setUserOrders(List<UserOrderBean> userOrders) {
		this.userOrders = userOrders;
	}

	public UserOrderBean getUserOrder() {
		return userOrder;
	}

	public void setUserOrder(UserOrderBean userOrder) {
		this.userOrder = userOrder;
	}

	public UserHistoryOrderBean getUserHistoryOrder() {
		return userHistoryOrder;
	}

	public void setUserHistoryOrder(UserHistoryOrderBean userHistoryOrder) {
		this.userHistoryOrder = userHistoryOrder;
	}

	public List<UserHistoryOrderBean> getUserHistoryOrders() {
		return userHistoryOrders;
	}

	public void setUserHistoryOrders(
			List<UserHistoryOrderBean> userHistoryOrders) {
		this.userHistoryOrders = userHistoryOrders;
	}

}
